import datetime
from time import strftime, localtime

import connector.driver
from connector.driver import DatabaseDriver


# 根据用户名和书籍号查询借书情况
def get_borrow_by_user_id_and_book_id(driver: DatabaseDriver, user_id: int, book_id: int)->tuple:
    return driver.query_one("select * from borrow where user_id={} and book_id={}".format(user_id,book_id))

def re_borrow(driver: DatabaseDriver, user_id: int, book_id: int)->int:
    current_time: str = str(strftime('%Y-%m-%d %H:%M:%S', localtime()))
    # 重新借书
    return driver.update('update borrow set borrow_time="{}",return_time=null where user_id={} and book_id={}'.format(current_time,user_id,book_id))

# 借书
def borrow_book(driver: DatabaseDriver, user_id: int, book_id: int)->int:
    current_time: str = str(strftime('%Y-%m-%d %H:%M:%S', localtime()))
    return driver.insert(
        'insert into borrow(user_id, book_id, borrow_time) values ({},{},"{}")'.format(user_id, book_id, current_time))

def delete_borrow(driver: DatabaseDriver, user_id: int, book_id: int)->int:
    return driver.delete("delete from borrow where user_id={} and book_id={}".format(user_id,book_id))

# 还书
def return_book(driver: DatabaseDriver, user_id: int, book_id: int)->int:
    current_time: str = str(strftime('%Y-%m-%d %H:%M:%S', localtime()))
    return driver.update('update borrow set return_time="{}" where user_id={} and book_id={}'.format(current_time,user_id,book_id))


# 检索借书信息
def borrow_list(driver: DatabaseDriver,user_text:str=None,book_text:str=None,user_id:int=None,book_id:int=None)->list:
    if user_text is None and book_text is None and user_id is None and book_id is None:
        return driver.query_all("select user.user_id,user.user_name,book.book_id,book_name,borrow_time,return_time from borrow,user,book where borrow.book_id = book.book_id and borrow.user_id = user.user_id")
    else:
        if user_text is not None and book_text is not None:
            return driver.query_all(
                'select user.user_id,user.user_name,book.book_id,book_name,borrow_time,return_time from borrow,user,book where borrow.book_id = book.book_id and borrow.user_id = user.user_id and (book.book_name like "%{}%" or book.publisher like "%{}%" or book.author like "%{}%" or user.user_name like "%{}%")'.format(book_text,book_text,book_text,user_text))
        elif user_text is not None:
            if book_id is None:
                return driver.query_all(
                    'select user.user_id,user.user_name,book.book_id,book_name,borrow_time,return_time from borrow,user,book where borrow.book_id = book.book_id and borrow.user_id = user.user_id and user.user_name like "%{}%"'.format(
                         user_text))
            else:
                return driver.query_all(
                    'select user.user_id,user.user_name,book.book_id,book_name,borrow_time,return_time from borrow,user,book where borrow.book_id = book.book_id and borrow.user_id = user.user_id and user.user_name like "%{}%" and book.book_id={}'.format(
                        user_text,book_id))
        elif book_text is not None:
            if user_id is None:
                return driver.query_all(
                    'select user.user_id,user.user_name,book.book_id,book_name,borrow_time,return_time from borrow,user,book where borrow.book_id = book.book_id and borrow.user_id = user.user_id and (book.book_name like "%{}%" or book.publisher like "%{}%" or book.author like "%{}%" )'.format(
                        book_text, book_text, book_text))
            else:
                return driver.query_all(
                    'select user.user_id,user.user_name,book.book_id,book_name,borrow_time,return_time from borrow,user,book where borrow.book_id = book.book_id and borrow.user_id = user.user_id and (book.book_name like "%{}%" or book.publisher like "%{}%" or book.author like "%{}%" ) and user.user_id = {}'.format(
                        book_text, book_text, book_text,user_id))
        elif user_id is not None and book_id is not None:
            return driver.query_all(
                "select user.user_id,user.user_name,book.book_id,book_name,borrow_time,return_time from borrow,user,book where borrow.book_id = book.book_id and borrow.user_id = user.user_id and book.book_id = {} and user.user_id={}".format(book_id,user_id))
        elif user_id is not None:
            return driver.query_all(
                "select user.user_id,user.user_name,book.book_id,book_name,borrow_time,return_time from borrow,user,book where borrow.book_id = book.book_id and borrow.user_id = user.user_id and user.user_id={}".format(
                     user_id))
        elif book_id is not None:
            return driver.query_all(
                "select user.user_id,user.user_name,book.book_id,book_name,borrow_time,return_time from borrow,user,book where borrow.book_id = book.book_id and borrow.user_id = user.user_id and book.book_id = {}".format(
                    book_id))
        else:
            return None